#!/usr/bin/env python
# -*- coding:utf-8 -*-
import configparser
import json
from typing import List

from conn.MyRedisFactory import RedisConfig
from excel.LFREE.LfreeBaseTabTop import LfreeBaseTabTop
from excel.LFREE.LfreeDictUtil import LfreeDictUtil as DictUtil, LfreeDictUtil
from excel.LFREE.LfreeObj import LfreeObj
from excel.PruTuutDb import PruTuutDb

f = r'setting.ini'
config = configparser.RawConfigParser()
config.read(f, encoding='UTF8')
rds_cfg = RedisConfig(host=config['redis']['host'],
                      port=config['redis']['port'],
                      passwd=config['redis']['password'])
rds_conn = RedisConfig.get_pool(rds_cfg)

base_table_top = LfreeBaseTabTop()
pru_tuut_db_conn = PruTuutDb.get_pool()
dict_db = DictUtil(pru_tuut_db_conn)

# **
# 参考计算EXCEL原文件中列表右侧的数据列
# 释义 =======> 对应列 =======> 公式
# bonus base =======> K59 =======> VLOOKUP(A66,dataL!A4:DY103,C34,TRUE)
# bonus rate =======> L59 =======> VLOOKUP(A66,dataL!A4:DY103,C35,TRUE)
# bonus =======> M59 =======> IF(L66>0,K66*L66^C32*C7/C30,0)
# death =======> N59 =======> C7+M66
# GCV base =======> O59 =======> VLOOKUP(A66,dataL!A4:JM103,C36,TRUE)
# GCV rate =======> P59 =======> VLOOKUP(A66,dataL!A4:JM103,C37,TRUE)
# GCV value =======> Q59 =======> IF(P66>0,O66*P66^C32*C7/C30*C24,0)
# CV =======> R59 =======> M66+Q66
# SA 0 =======> S59 =======> C7
# SA 1 =======> T59 =======> IF(C17>0,IF((AB66-C8+1)>0,C7*(1+C18)^1,0),0)
# SA 2 =======> U59 =======> IF(C17>1,IF((AB66-C8*2+1)>0,C7*(1+C18)^2,0),0)
# SA 3 =======> V59 =======> IF(C17>2,IF((AB66-C8*3+1)>0,C7*(1+C18)^3,0),0)
# SA 4 =======> W59 =======> IF(C17>3,IF((AB66-C8*4+1)>0,C7*(1+C18)^4,0),0)
# SA 5 =======> X59 =======> IF(C17>4,IF((AB66-C8*5+1)>0,C7*(1+C18)^5,0),0)
# Total min SA =======> Y59 =======> (ROUNDDOWN(MIN(AB66,C8*(1+C17)-1)/C8,0)+1)*C49
# Running SA =======> Z59 =======> SUM(S66:X66)*AG65
# SA too low? =======> AA59 =======> Z66<Y66
# Year =======> AB59 =======> B66-C3
# Rebate =======> AC59 =======> IF(LOWER(F10)="y",0,0)
# EGS premium =======> AD59 =======> IF(B66<102,AI66,0)*C45-AC66
# Cumulative premium =======> AE59 =======> AE65+AD66
# Annual withdrawal % =======> AF59 =======> IF(AR66>0,(E66/C45)/AR66,0)
# Remain % =======> AG59 =======> AG65-AF66
# Balance =======> AH59 =======> AG66*AR66
# LFREE premium =======> AI59 =======> IF(B66-C3<(C8*(C17+1)),C39*(1+C18)^ROUNDDOWN((B66-C3)/C8,0),0)
# LFREE CV =======> AJ59 =======> R66
# Adjusted EGS CV =======> AK59 =======> AJ66
# A. EGS CV 1 =======> AL59 =======> IF(C17>0,IF((AB66-C8+1)>0,VLOOKUP(B66-C8,B61:AK161,C41,TRUE)*(1+C18)^1,0),0)
# A. EGS CV 2 =======> AM59 =======> IF(C17>1,IF((AB66-C8*2+1)>0,VLOOKUP(B66-C8*2,B61:AK161,C41,TRUE)*(1+C18)^2,0),0)
# A. EGS CV 3 =======> AN59 =======> IF(C17>2,IF((AB66-C8*3+1)>0,VLOOKUP(B66-C8*3,B61:AK161,C41,TRUE)*(1+C18)^3,0),0)
# A. EGS CV 4 =======> AO59 =======> IF(C17>3,IF((AB66-C8*4+1)>0,VLOOKUP(B66-C8*4,B61:AK161,C41,TRUE)*(1+C18)^4,0),0)
# A. EGS CV 5 =======> AP59 =======> IF(C17>4,IF((AB66-C8*5+1)>0,VLOOKUP(B66-C8*5,B61:AK161,C41,TRUE)*(1+C18)^5,0),0)
# Total A. EGS CV =======> AQ59 =======> SUM(AK66:AP66)
# re-adjusted CV =======> AR59 =======> IF(AA66,AR65*(1+Notes!B3),AQ66)
# Balance before withdraw =======> AS59 =======> AR66*AG65
# Retired? =======> AT59 =======> (B66>=F5)
# Income? =======> AU59 =======> IF(AT66,IF(F8>0,AND(B66<F3,(B66-F5)<F8),B66<F3),FALSE)
# Inflation factor =======> AV59 =======> IF(AT66,(1+F7)^(B66-F5),1)
# Inflated income =======> AW59 =======> IF(AU66,F6/C45*AV66,0)
# Withdrawal =======> AX59 =======> MIN(AW66+D66/C45,AS66)
# Cashflow =======> AY59 =======> IF(F3>C3,IF(B66<F3,E66-C66,E66+G66),0)
# ----------------------------------------------------------------------------------------------------------
TEST_YEAR = range(100)  # 测试年限为100的数据


# ----------------------------------------------------------------------------------------------------------
class LfreeBaseTabCalStd:
    LfreeBaseTabCalStd = {
        "bonus_base": 0,
        "bonus_rate": 0,
        "bonus": 0,
        "death": 0,
        "gcv_base": 0,
        "gcv_rate": 0,
        "gcv_value": 0,
        "cv": 0,
        "sa_0": 0,
        "sa_1": 0,
        "sa_2": 0,
        "sa_3": 0,
        "sa_4": 0,
        "sa_5": 0,
        "total_min_sa": 0,
        "running_sa": 0,
        "sa_too_low": 0,
        "year": 0,
        "rebate": 0,
        "egs_premium": 0,
        "cumulative_premium": 0,
        "annual_withdrawal": 0,
        "remain": 0,
        "balance": 0,
        "lfree_premium": 0,
        "lfree_cv": 0,
        "adjusted_egs_cv": 0,
        "a_egs_cv_1": 0,
        "a_egs_cv_2": 0,
        "a_egs_cv_3": 0,
        "a_egs_cv_4": 0,
        "a_egs_cv_5": 0,
        "total_a_egs_cv": 0,
        "re-adjusted_cv": 0,
        "balance_before_withdraw": 0,
        "retired": 0,
        "income": 0,
        "inflation_factor": 0,
        "inflated_income": 0,
        "withdrawal": 0,
        "cashflow": 0,
    }

    res = []

    def __init__(self):
        return

    # **
    # 计算 bonus base
    # Params:
    # 年期 A : Year
    # 供款期 C8 : Payment_Term
    # 翌年歲 C3 : anb
    def get_bonus_base(self, year, anb, payment_term, anb_index) -> res:
        # 参考计算公式
        # VLOOKUP(A66,dataL!A4:DY103,C34,TRUE)
        res = dict_db.vlookup_by_year_and_anb_and_payment_term(args=[year, anb, payment_term, anb_index])
        if res['code'] == LfreeDictUtil.SUCCESS:
            return res['data']
        elif res['code'] == LfreeDictUtil.LOST_DATA:
            return round(float(0), 5)
        return float(0)

    # **
    # 计算 bonus rate
    # Params:
    # 年期 A : Year
    # 供款期 C8 : Payment_Term
    # 翌年歲 C3 : anb
    def get_bonus_rate(self, year, anb, payment_term) -> res:
        # 参考计算公式
        # VLOOKUP(A66,dataL!A4:DY103,C35,TRUE)
        # STEP CHECK ROW  1 [ C35 ==> IF(AI5<>0,(AI5/AH5)^(1/(AI$2-AH$2)),AX4) ]
        #                 2 [ AI ==> LFREE 基础数据表中，根据年限，index查询数据下一个周期数据 ]
        #                 3 [ AH ==> LFREE 基础数据表中，根据年限，index查询数据 ]
        #                 4 [ AI$2 ==> 当前and所在的index ]
        #                 5 [ AH$2 ==> 当前and所在的下一个index ]
        # 周期数据计算
        # current_index = LfreeDictUtil.get_anb_index(anb)  # 当前anb所在周期
        # next_index = LfreeObj.ANB_CASE[LfreeObj.ANB_CASE.index(current_anb_index) + 1]
        current_anb_index = dict_db.get_cal_anb_index(anb=anb)  # 当前anb周期索引值
        next_anb_index = current_anb_index + LfreeObj.LFREE_RATE_AROUND  # 下一个anb周期索引值
        # 当前周期内的base值
        current_stdd = dict_db.vlookup_by_year_and_anb_and_payment_term(args=[year, anb, payment_term,
                                                                              current_anb_index])['data']
        # 获取下一个周期的 std值
        next_stdd = dict_db.vlookup_by_year_and_anb_and_payment_term(args=[year, next_anb_index, payment_term,
                                                                           next_anb_index])['data']
        # STEP 2 (AI5/AH5)^(1/(AI$2-AH$2))
        try:
            if next_stdd is None:
                year = year - 1
                return self.get_bonus_rate(year, anb, payment_term)
            rate = (next_stdd / current_stdd) ** (1 / (next_anb_index - current_anb_index))
            return round(rate, 9)
        except ZeroDivisionError:
            return 0
        # current_res_rate_array = []
        # for _rate_value_index in range(len(current_rate_value)):
        #     next_res_data = next_rate_value[_rate_value_index]
        #     current_res_data = current_rate_value[_rate_value_index]
        #     if current_res_data == 0 and _rate_value_index == 0:
        #         current_res_rate_array.append(float(0))
        #         continue
        #     else:
        #         if current_res_data == 0 and _rate_value_index > 0:
        #             last_data_index = len(current_res_rate_array) - 1
        #             current_res_rate_array.append(current_res_rate_array[last_data_index])
        #             continue
        #     # (AI8/AH8)^(1/(AI$2-AH$2))
        #     n = (next_res_data / current_res_data)
        #     m = 1 / LfreeObj.LFREE_RATE_AROUND
        #     current_res_rate_array.append(round((n ** m), 9))
        # return current_res_rate_array

    # **
    # 计算 bonus
    # Params:
    # 投保額 C7 : sum_assured
    # 年龄补偿 C32 : age_offset
    # 红利率 l : bonus rate
    # 红利基准 k : bonus base
    # 理论保额 C30 : data S.A.
    @staticmethod
    def get_bonus(l, k, age_offset, sum_assured) -> res:
        # 参考计算公式
        # IF(L66>0,K66*L66^C32*C7/C30,0)
        if not l > 0:
            return 0
        else:
            # K66 * L66 ^ C32 * C7 / C30
            # STEP 1 L66 ^ C32 计算复利
            cal_val = DictUtil.spow(l, age_offset)
            # STEP 2 K66 * cal_val * C7 / C30
            res = k * cal_val * sum_assured / LfreeObj.DATA_S_A
            return int(round(res, 0))

    # **
    # 计算 death
    # params:
    # 投保額 C7 : sum_assured
    # 红利 M66 : bonus
    @staticmethod
    def get_death(sum_assured, bonus):
        # 参考计算公式
        # C7+M66
        return sum_assured + bonus

    # **
    # 计算 gcv_base
    # Params:
    # 年期 A : Year
    # 供款期 C8 : Payment_Term
    # 翌年歲 C3 : anb
    def get_gcv_base(self, year, anb, payment_term, anb_index) -> res:
        # 参考计算公式
        # VLOOKUP(A66,dataL!A4:JM103,C36,TRUE)
        # DictUtil.VLOOKUP(std=A66, area=[A4, JM103], target=C36)
        res = dict_db.vlookup_gcv_by_year_and_anb_and_payment_term(args=[year, anb, payment_term, anb_index])
        if res['code'] == LfreeDictUtil.SUCCESS:
            return res['data']
        elif res['code'] == LfreeDictUtil.LOST_DATA:
            return round(float(0), 5)
        return float(0)

    def get_gcv_rate(self, year, anb, payment_term) -> res:
        # 参考计算公式
        # VLOOKUP(A66,dataL!A4:JM103,C37,TRUE)
        # STEP CHECK ROW  1 [ C37 ==> IF(FW4<>0,(FW4/FV4)^(1/(FW$2-FV$2)),GL3) ]
        #                 2 [ FW ==> LFREE GCV 基础数据表中，根据年限，index查询数据下一个周期数据 ]
        #                 3 [ FV ==> LFREE GCV 基础数据表中，根据年限，index查询数据 ]
        #                 4 [ FV$2 ==> 当前and所在的index ]
        #                 5 [ FW$2 ==> 当前and所在的下一个index ]
        current_anb_index = dict_db.get_cal_anb_index(anb=anb)  # 当前anb周期索引值
        # 获取下一个周期的 std值
        next_anb_index = current_anb_index + LfreeObj.LFREE_RATE_AROUND  # 下一个anb周期索引值
        # 当前周期内的base值
        current_stdd = dict_db.vlookup_gcv_by_year_and_anb_and_payment_term(args=[year, anb, payment_term,
                                                                                  current_anb_index])['data']
        next_stdd = dict_db.vlookup_gcv_by_year_and_anb_and_payment_term(args=[year, next_anb_index, payment_term,
                                                                               next_anb_index])['data']
        # STEP 2 (FW4/FV4)^(1/(FW$2-FV$2))
        try:
            if next_stdd is None:
                year = year - 1
                return self.get_gcv_rate(year, anb, payment_term)
            # 周期数据差计算
            rate = (next_stdd / current_stdd) ** (1 / (next_anb_index - current_anb_index))
            return round(rate, 7)
        except ZeroDivisionError:
            return 0

    # **
    # 计算 gcv_base
    # Params:
    # 年期 P66 : gcv_rate
    # 年期 O66 : gcv_base
    # 年龄补偿 C32 : age_offset
    # 投保額 C7 : sum_assured
    # 翌年歲 C3 : anb
    # 理论保额 C30 : data S.A.
    # 理论保额 C24 : premium ratio
    # 性別 C4 : Gender
    # 吸煙者 C5 : Smoker
    # 供款期 C8 : Payment Term
    def get_gcv_value(self, gcv_rate, gcv_base, age_offset, sum_assured, args: List[str]):
        # 参考计算公式
        # IF(P62>0,O62*P62^$C$32*$C$7/$C$30*$C$24,0)
        if not gcv_rate > 0:
            return 0
        else:
            # O62*P62^$C$32*$C$7/$C$30*$C$24
            # STEP 1 O62*P62^$C$32*$C$7
            cal_val = gcv_base * gcv_rate ** age_offset * sum_assured
            # STEP 2 cal_val/C30*C24
            res = cal_val / LfreeObj.DATA_S_A * base_table_top.get_premium_ratio(args)
            return round(res, 0)

    # **
    # 计算 CV
    # Params:
    # 红利 M : bonus
    # 最小化广义交叉 Q : GCV value
    def get_cv(self, bonus, GCV_value):
        # 参考计算公式
        # M61+Q61
        return bonus + GCV_value

    # **
    # 计算 sa
    # Params:
    # 投保額 C7 : Sum Assured
    def get_sa_0(self, C7):
        # 参考计算公式
        # C7
        return C7

    # **
    # 计算 SA 1
    # Params:
    # 投保額 C7 : Sum Assured
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # 年期 AB : Year
    # 投保額 C7 : Sum Assured
    # 供款期 C8 : Payment Term
    def get_sa_1(self, repeat_plan, cal_year, payment_term, sum_assured, repeat_growth_rate):
        # 参考计算公式
        # IF(C17>0,IF((AB61-C8+1)>0,C7*(1+C18)^1,0),0)
        if not repeat_plan > 0:
            return 0
        else:
            # IF((AB66-C8+1)>0,C7*(1+C18)^1,0)
            if not (cal_year - payment_term + 1) > 0:
                return 0
            else:
                # C7*(1+C18)^1
                n = sum_assured * (1 + repeat_growth_rate)
                m = 1
                return DictUtil.spow(n, m)
        return

    # **
    # 计算 SA 2
    # Params:
    # 投保額 C7 : Sum Assured
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # 年期 AB : Year
    # 投保額 C7 : Sum Assured
    # 供款期 C8 : Payment Term
    def get_sa_2(self, repeat_plan, cal_year, payment_term, sum_assured, repeat_growth_rate):
        # 参考计算公式
        # IF(C17>1,IF((AB61-C8*2+1)>0,C7*(1+C18)^2,0),0)
        if not repeat_plan > 1:
            return 0
        else:
            # IF((AB61-C8*2+1)>0,C7*(1+C18)^2,0)
            # (AB61-C8*2+1)>0
            if not (cal_year - payment_term * 2 + 1) > 0:
                return 0
            else:
                # C7*(1+C18)^2
                return sum_assured * (1 + repeat_growth_rate) ** 2

    # **
    # 计算 SA 3
    # Params:
    # 投保額 C7 : Sum Assured
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # 年期 AB : Year
    # 投保額 C7 : Sum Assured
    # 供款期 C8 : Payment Term
    def get_sa_3(self, repeat_plan, cal_year, payment_term, sum_assured, repeat_growth_rate):
        # 参考计算公式
        # IF(C17>2,IF((AB61-C8*3+1)>0,C7*(1+C18)^3,0),0)
        if not repeat_plan > 2:
            return 0
        else:
            # IF((AB61-C8*3+1)>0,C7*(1+C18)^3,0)
            # (AB61-C8*3+1)>0
            if not (cal_year - payment_term * 3 + 1) > 0:
                return 0
            else:
                # C7*(1+C18)^3
                return sum_assured * (1 + repeat_growth_rate) ** 3

    # **
    # 计算 SA 4
    # Params:
    # 投保額 C7 : Sum Assured
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # 年期 AB : Year
    # 投保額 C7 : Sum Assured
    # 供款期 C8 : Payment Term
    def get_sa_4(self, repeat_plan, cal_year, payment_term, sum_assured, repeat_growth_rate):
        # 参考计算公式
        # IF(C17>3,IF((AB61-C8*4+1)>0,C7*(1+C18)^4,0),0)
        if not repeat_plan > 3:
            return 0
        else:
            # IF((AB66-C8*4+1)>0,C7*(1+C18)^4,0)
            # (AB66-C8*4+1)>0
            if not (cal_year - payment_term * 4 + 1) > 0:
                return 0
            else:
                # C7*(1+C18)^4
                return sum_assured * (1 + repeat_growth_rate) ** 4

    # **
    # 计算 SA 5
    # Params:
    # 投保額 C7 : Sum Assured
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # 年期 AB : Year
    # 投保額 C7 : Sum Assured
    # 供款期 C8 : Payment Term
    def get_sa_5(self, repeat_plan, cal_year, payment_term, sum_assured, repeat_growth_rate):
        # 参考计算公式
        # IF(C17>4,IF((AB61-C8*5+1)>0,C7*(1+C18)^5,0),0)
        if not repeat_plan > 4:
            return 0
        else:
            # IF((AB66-C8*5+1)>0,C7*(1+C18)^5,0)
            # (AB66-C8*5+1)>0
            if not (cal_year - payment_term * 5 + 1) > 0:
                return 0
            else:
                # C7*(1+C18)^5
                return sum_assured * (1 + repeat_growth_rate) ** 5

    # **
    # 计算 Total min SA
    # Params:
    # 重複計劃 C17 : Repeat Plan
    # 年期 AB : Year
    # 供款期 C8 : Payment Term
    # C49 : sa_minimum
    def get_total_min_sa(self, cal_year, payment_term, repeat_plan, sa_minimum):
        # 参考计算公式
        # (ROUNDDOWN(MIN(AB61,C8*(1+C17)-1)/C8,0)+1)*C49
        # STEP 1 MIN(AB66,C8*(1+C17)-1)
        numbers = []
        numbers.append(cal_year)
        numbers.append(payment_term * (1 + repeat_plan) - 1)
        # STEP 2 ROUNDDOWN(MIN(AB66,C8*(1+C17)-1)/C8,0)
        cal_val = min(numbers) // payment_term
        # STEP 3 (ROUNDDOWN(MIN(AB66,C8*(1+C17)-1)/C8,0)+1)*C49
        return (cal_val + 1) * sa_minimum

    # **
    # 计算 Running SA
    # Params:
    # 重複計劃 S : SA0
    # 年期 X : SA5
    # 余留 AG : Remain %
    def get_running_sa(self, nums, last_remain):
        # 参考计算公式
        # SUM(S66:X66)*AG60
        return sum(nums) * last_remain

    # **
    # 计算 SA too low
    # Params:
    # 余留 Y : Total min SA
    # -- Z : Running SA
    def get_sa_too_low(self, running_sa, total_min_sa):
        # 参考计算公式
        # Z61<Y61
        return running_sa < total_min_sa

    # **
    # 计算 year
    # Params:
    # 翌年歲 B : Year
    # 翌年歲 C3 : anb
    def get_year(self, year, anb):
        # 参考计算公式
        # B60+1
        # STEP CHECK ROW  1 [ B60 ==> C3-1 ]
        #                 2 [ year == > B60+1]
        return (anb + year) - anb if not year == 0 else ((anb - 1) + 1) - anb

    # **
    # 计算 Rebate 返利
    # Params:
    # 推廣優惠 F10 : Promotion
    # 供款期 C8 : Payment Term
    # 汇率 C45 : Display_rate
    # 保费 AI : LFREE premium
    def get_rebate(self, cal_year, promotion, payment_term, lfree_premium, display_rate):
        rebate = None
        # 参考计算公式
        if promotion.upper() == "N":
            return 0
        if cal_year == 0:
            # CASE 首年 IF(LOWER(F10)="y",IF(C8=1,AI61*C45*IF(AI61>=125000,4%,IF(AI61>=62500,3%,IF(AI61>=25000,2%,0%))),0),0)
            # IF(C8=1,AI61*C45*IF(AI61>=125000,4%,IF(AI61>=62500,3%,IF(AI61>=25000,2%,0%))),0)
            if not payment_term == 1:
                return 0
            else:
                # STEP 1 IF(AI61>=125000,4%,IF(AI61>=62500,3%,IF(AI61>=25000,2%,0%)))
                if lfree_premium >= 12500:
                    cal_rate = 4 / 100
                elif lfree_premium >= 62500:
                    cal_rate = 3 / 100
                elif lfree_premium >= 25000:
                    cal_rate = 2 / 100
                else:
                    cal_rate = 0
                # STEP 2 AI61*C45*cal_rate
                rebate = lfree_premium * display_rate * cal_rate
        elif cal_year == 1:
            # CASE 次年
            # IF(LOWER(F10)="y",IF(C8>1,AI62*C45*30%,0),0)
            # IF(C8 > 1, AI62 * C45 * 30 %, 0)
            if payment_term > 1:
                return lfree_premium * display_rate * 30 / 100
            else:
                return 0
        else:
            return 0
        return rebate

    # **
    # 计算 EGS保費 EGS premium
    # Params:
    # anb B61 : anb_year
    # lfree 保费 AI61 : LFREE premium
    # 利率 C45 : Display rate
    # 返利 AC61 : Rebate
    def get_egs_premium(self, anb_year, lfree_premium, display_rate, rebate):
        # 参考计算公式
        # IF(B61<102,AI61,0)*C45-AC61
        # STEP 1 IF(B61<102,AI61,0)
        cal_val = lfree_premium if anb_year < 102 else 0
        # STEP 2 cal_val*C45-AC66
        return cal_val * display_rate - rebate

    # **
    # 计算 累计保费 Cumulative premium
    # Params:
    # EGS保費 AD61 : EGS_premium
    # 累计保费 保费 AE60 : last_Cumulative_premium
    def get_cumulative_premium(self, last_Cumulative_premium, EGS_premium):
        # 参考计算公式
        # AE60+AD61
        return last_Cumulative_premium + EGS_premium

    # **
    # 计算 Annual withdrawal %
    # Params:
    # 重新调整CV AR61 : re-adjusted CV
    # 總提取 E61 : Total Withdrawal
    # 汇率 C45 : Display_rate
    def get_annual_withdrawal(self, total_withdrawal, display_rate, re_adjusted_CV):
        # 参考计算公式
        # IF(AR61>0,(E61/C45)/AR61,0)
        return ((total_withdrawal / display_rate) / re_adjusted_CV) if re_adjusted_CV > 0 else 0

    # **
    # 计算 year
    # Params:
    # 翌年歲 AG : remain %
    # 翌年歲 AF : Annual withdrawal %
    def get_remain(self, cal_year, last_remain, annual_withdrawal):
        # 参考计算公式
        # AG65-AF66
        # 这里做一个首年判断,首年时候,remain的值是100,次年remain = 上一年remain-当前annual_withdrawal
        return last_remain - annual_withdrawal if not cal_year == 0 else 1

    # **
    # 计算 year
    # Params:
    # 剩余 AG : Remain %
    # 调整值 AR : re-adjusted CV
    def get_Balance(self, remain, re_adjusted_CV):
        # 参考计算公式
        # AG66*AR66
        return remain * re_adjusted_CV

    # **
    # 计算 LFREE premium
    # Params:
    # 翌年歲_year B61 : anb_year
    # 翌年歲 C3 : anb
    # 供款期 C8 : Payment Term
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # LFREE年度保费 C39 : LFREE annual premium
    def get_lfree_premium(self, anb_year, anb, payment_term, repeat_plan, lfree_annual_premium, repeat_growth_rate):
        # 参考计算公式
        # IF(B61-C3<(C8*(C17+1)),C39*(1+C18)^ROUNDDOWN((B61-C3)/C8,0),0)
        if not anb_year - anb < (payment_term * (repeat_plan + 1)):
            return 0
        else:
            # C39*(1+C18)^ROUNDDOWN((B66-C3)/C8,0)
            return lfree_annual_premium * (1 + repeat_growth_rate) ** ((anb_year - anb) // payment_term)

    # **
    # 计算 LFREE CV
    # Params:
    # -- R : CV
    def get_lfree_cv(self, cv):
        # 参考计算公式
        # R66
        return cv

    # **
    # 计算 Adjusted EGS CV
    # Params:
    # -- AJ : LFREE CV
    def get_adjusted_egs_cv(self, lfree_cv):
        # 参考计算公式
        # AJ66
        return lfree_cv

    # **
    # 计算 A. EGS CV 1
    # Params:
    # 翌年歲_year B61 : anb_year
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # 年期 AB : Year
    # Adjusted EGS CV ----> VLOOKUP(B61-C8,B61:AK161,C41,TRUE)
    def get_a_egs_cv_1(self, repeat_plan, ab_year, payment_term, repeat_growth_rate, current_adjusted_egs_cv):
        # 参考计算公式
        # IF(C17>0,IF((AB61-C8+1)>0,VLOOKUP(B61-C8,B61:AK161,C41,TRUE)*(1+C18)^1,0),0)
        if not repeat_plan > 0:
            return 0
        else:
            # IF((AB61-C8+1)>0,VLOOKUP(B61-C8,B61:AK161,C41,TRUE)*(1+C18)^1,0)
            if not (ab_year - payment_term + 1) > 0:
                return 0
            else:
                # VLOOKUP(B61-C8,B61:AK161,C41,TRUE) = Adjusted EGS CV
                # FROM VLOOKUP(B61-C8,B61:AK161,C41,TRUE)*(1+C18)^1 TO current_adjusted_egs_cv * (1 + C18) ^1
                # STEP 1 current_adjusted_egs_cv * (1 + C18) ^1
                return current_adjusted_egs_cv * (1 + repeat_growth_rate) ** 1

    # **
    # 计算 A. EGS CV 2
    # Params:
    # 翌年歲_year B61 : anb_year
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # 年期 AB : Year ab_year
    # Adjusted EGS CV ----> VLOOKUP(B61-C8,B61:AK161,C41,TRUE)
    def get_a_egs_cv_2(self, repeat_plan, ab_year, payment_term, repeat_growth_rate, current_adjusted_egs_cv):
        # 参考计算公式
        # IF(C17>1,IF((AB66-C8*2+1)>0,VLOOKUP(B66-C8*2,B61:AK161,C41,TRUE)*(1+C18)^2,0),0)
        if not repeat_plan > 1:
            return 0
        else:
            # IF((AB66-C8*2+1)>0,VLOOKUP(B66-C8*2,B61:AK161,C41,TRUE)*(1+C18)^2,0)
            if not (ab_year - payment_term * 2 + 1) > 0:
                return 0
            else:
                # VLOOKUP(B66-C8*2,B61:AK161,C41,TRUE)*(1+C18)^2
                # STEP 1 current_adjusted_egs_cv * (1 + C18) ^ 2
                return current_adjusted_egs_cv * (1 + repeat_growth_rate) ** 2

    # **
    # 计算 A. EGS CV 3
    # Params:
    # 翌年歲_year B61 : anb_year
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # 年期 AB : Year ab_year
    # Adjusted EGS CV ----> VLOOKUP(B61-C8,B61:AK161,C41,TRUE)
    def get_a_egs_cv_3(self, repeat_plan, ab_year, payment_term, repeat_growth_rate, current_adjusted_egs_cv):
        # 参考计算公式
        # IF(C17>2,IF((AB66-C8*3+1)>0,VLOOKUP(B66-C8*3,B61:AK161,C41,TRUE)*(1+C18)^3,0),0)
        if not repeat_plan > 2:
            return 0
        else:
            # IF((AB66-C8*3+1)>0,VLOOKUP(B66-C8*3,B61:AK161,C41,TRUE)*(1+C18)^3,0)
            if not (ab_year - payment_term * 3 + 1) > 0:
                return 0
            else:
                # VLOOKUP(B66-C8*3,B61:AK161,C41,TRUE)*(1+C18)^3
                # STEP 1 current_adjusted_egs_cv * (1 + C18) ^ 3
                return current_adjusted_egs_cv * (1 + repeat_growth_rate) ** 3

    # **
    # 计算 A. EGS CV 4
    # Params:
    # 翌年歲_year B61 : anb_year
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # 年期 AB : Year ab_year
    # Adjusted EGS CV ----> VLOOKUP(B61-C8,B61:AK161,C41,TRUE)
    def get_a_egs_cv_4(self, repeat_plan, ab_year, payment_term, repeat_growth_rate, current_adjusted_egs_cv):
        # 参考计算公式
        # IF(C17>3,IF((AB66-C8*4+1)>0,VLOOKUP(B66-C8*4,B61:AK161,C41,TRUE)*(1+C18)^4,0),0)
        if not repeat_plan > 3:
            return 0
        else:
            # IF((AB66-C8*4+1)>0,VLOOKUP(B66-C8*4,B61:AK161,C41,TRUE)*(1+C18)^4,0)
            if not (ab_year - payment_term * 4 + 1) > 0:
                return 0
            else:
                # VLOOKUP(B66-C8*4,B61:AK161,C41,TRUE)*(1+C18)^4
                # STEP 1 current_adjusted_egs_cv * (1 + C18) ^ 4
                return current_adjusted_egs_cv * (1 + repeat_growth_rate) ** 4

    # **
    # 计算 A. EGS CV 5
    # Params:
    # 翌年歲_year B61 : anb_year
    # 重複計劃 C17 : Repeat Plan
    # 重複增長率 C18 : Repeat Growth Rate
    # 年期 AB : Year
    # Adjusted EGS CV ----> VLOOKUP(B61-C8,B61:AK161,C41,TRUE)
    def get_a_egs_cv_5(self, repeat_plan, ab_year, payment_term, repeat_growth_rate, current_adjusted_egs_cv):
        # 参考计算公式
        # IF(C17>4,IF((AB66-C8*5+1)>0,VLOOKUP(B66-C8*5,B61:AK161,C41,TRUE)*(1+C18)^5,0),0)
        if not repeat_plan > 4:
            return 0
        else:
            # IF((AB66-C8*5+1)>0,VLOOKUP(B66-C8*5,B61:AK161,C41,TRUE)*(1+C18)^5,0)
            if not (ab_year - payment_term * 5 + 1) > 0:
                return 0
            else:
                # VLOOKUP(B66-C8*5,B61:AK161,C41,TRUE)*(1+C18)^5
                # STEP 1 current_adjusted_egs_cv * (1 + C18) ^ 5
                return current_adjusted_egs_cv * (1 + repeat_growth_rate) ** 5

    # **
    # 计算 re-adjusted CV
    # Params:
    # -- AK : Adjusted EGS CV
    # -- AP : A. EGS CV 5
    def get_total_a_egs_cv(self, nums):
        # 参考计算公式
        # SUM(AK66:AP66)
        return sum(nums)

    # **
    # 计算 重新调整CV re-adjusted CV
    # Params:
    # -- AR60 : last-re-adjusted CV
    # -- AA61 : SA too low
    # -- AQ61 : Total A. EGS CV
    def get_re_adjusted_cv(self, last_re_adjusted_cv, sa_too_low, total_a_egs_cv):
        # 参考计算公式
        # IF(AA61,AR60*(1+Notes!B3),AQ61)
        # STEP 1 cal_sa_too_low
        return last_re_adjusted_cv * (1 + LfreeObj.RETURN_AFTER_SURRENDER) if sa_too_low else total_a_egs_cv

    # **
    # 计算 提款前余额 Balance before withdraw
    # Params:
    # 调整值 AR : last-re-adjusted CV
    # 剩余 AG : Remain
    def get_balance_before_withdraw(self, re_adjusted_CV, remain):
        # 参考计算公式
        # AR66*AG65
        return re_adjusted_CV * remain

    # **
    # 退休领取 Retired
    # Params:
    # 翌年歲_year B61 : anb_year
    # 提款開始翌年歲 F5 : Withdrawal Starting ANB
    def get_retired(self, anb_year, withdrawal_starting_anb):
        # 参考计算公式
        # (B66>=F5)
        return anb_year >= withdrawal_starting_anb

    # **
    # 退休收入 Income
    # Params:
    # 退休领取 AT61 : Retired
    # 翌年歲_year B61 : anb_year
    # 退保翌年歲 F3 : Surrender ANB
    # 提款開始翌年歲 F5 : Withdrawal Starting ANB
    # 提取年期 F8 : Withdrawal Years
    def get_income(self, retired, anb_year, surrender_anb, withdrawal_starting_anb, withdrawal_years):
        # 参考计算公式
        # IF(AT61,IF(F8>0,AND(B61<F3,(B61-F5)<F8),B61<F3),FALSE)
        if not retired:
            return False
        else:
            # IF(F8>0,AND(B66<F3,(B66-F5)<F8),B66<F3)
            if not withdrawal_years > 0:
                return anb_year < surrender_anb
            else:
                return anb_year < surrender_anb & ((anb_year - withdrawal_starting_anb) < withdrawal_years)

    # **
    # 通货膨胀系数 Inflation factor
    # Params:
    # 退休领取 AT61 : Retired
    # 翌年歲_year B61 : anb_year
    # 退保翌年歲 F5 : Surrender ANB
    # 通脹率 F7 : Inflation
    def get_inflation_factor(self, retired, inflation, anb_year, withdrawal_starting_anb):
        # 参考计算公式
        # =IF(AT61,(1+F7)^(B61-F5),1)
        # STEP 1 (1 + F7)
        n = 1 + inflation
        # STEP 2 (B61-F5)
        m = anb_year - withdrawal_starting_anb
        # STEP 3 (1+F7)^(B61-F5)
        return n ** m if retired else 1

    # **
    # 通货膨胀的收入 Inflated income
    # Params:
    # 退休领取 AU61 : Income
    # 翌年歲_year AV61 : Inflation factor
    # 首年提款 F6 : 1st Year Withdrawal
    # 汇率 C45 : Display rate
    def get_inflated_income(self, income, fst_year_withdrawal, display_rate, inflation_factor, ):
        # 参考计算公式
        # =IF(AU61,F6/C45*AV61,0)
        return fst_year_withdrawal / display_rate * inflation_factor if income else 0

    # **
    # 提款 Withdrawal
    # Params:
    # 通货膨胀的收入 AW61 : Inflated income
    # 額外提取 D61 : Extra Withdrawal
    # 汇率 C45 : Display rate
    # 提款前余额 AS61 : Balance before withdraw
    def get_withdrawal(self, inflated_income, extra_withdrawal, display_rate, balance_before_withdraw, ):
        # 参考计算公式
        # =MIN(AW61+D61/C45,AS61)
        numbers = [(inflated_income + extra_withdrawal / display_rate), balance_before_withdraw]
        return min(numbers)

    # **
    # 现金流 Cashflow
    # Params:
    # 退保翌年歲 F3 : Surrender ANB
    # 翌年歲 C3 : anb
    # 翌年歲_year B61 : anb_year
    # 每年供款 C61 : Annual Contribution
    # 總提取 E61 : Total Withdrawal
    # 餘額 G61 : Balance
    def get_cashflow(self, surrender_anb, anb, anb_year, total_withdrawal, annual_contribution, balance, ):
        # 参考计算公式
        # =IF(F3>C3,IF(B61<F3,E61-C61,E61+G61),0)
        if not surrender_anb > anb:
            return 0
        else:
            return total_withdrawal - annual_contribution if anb_year < surrender_anb else total_withdrawal + balance

    # **
    # 说明:
    # 加入了提现计算处理
    # 提现 年份队列与提现金额队列相等
    # 每一个计算年中需要与提现年进行存在验证，如果存在则需要使用提现参数数据，否则当前计算年的提现数据默认为0
    @staticmethod
    def lfree_master(str_json_key, args: list[str], years, param_Extra_Withdrawal: list[float],
                     param_withdrawal_anb: list[int]):
        from datetime import datetime
        fst_year_withdrawal: int
        TEST_SELECT_PAYMENT = int(args[0])  # select_payment
        TEST_SELECT_ANB = int(args[1])  # select_anb
        TEST_SUM_ASSURED = int(args[2])  # sum_assured
        TEST_GENDER = args[3]  # gender
        TEST_SMOKER = args[4]  # smoker
        TEST_REPEAT_PLAN = int(args[5])  # repeat_plan
        TEST_REPEAT_GROWTH_RATE = float(args[6])  # repeat_growth_rate
        TEST_CURRENCY = args[7]  # currency
        TEST_CURRENCY = TEST_CURRENCY.replace("_", "-")
        TEST_PROMOTION = args[8]  # promotion
        TEST_WITHDRAWAL_STARTING_ANB = int(args[9])  # withdrawal_starting_anb
        TEST_SURRENDER_ANB = int(args[10])  # surrender_anb
        TEST_WITHDRAWAL_YEARS = int(args[11])  # withdrawal_years
        TEST_INFLATION = int(args[12])  # inflation
        TEST_FST_YEAR_WITHDRAWAL = int(args[13])  # fst_year_withdrawal

        calStd = LfreeBaseTabCalStd()
        current_anb_index = dict_db.get_cal_anb_index(anb=TEST_SELECT_ANB)
        try:
            # 获取外部参数
            cal_base_anb = base_table_top.get_base_anb(anb=TEST_SELECT_ANB)
            cal_age_offset = base_table_top.get_age_offset(anb=TEST_SELECT_ANB, base_anb=cal_base_anb)
            cal_exchange_rate = base_table_top.get_exchange_rate(currency=TEST_CURRENCY)
            cal_sa_minimum = base_table_top.get_sa_minimum(exchange_rate=cal_exchange_rate)
            cal_display_rate = base_table_top.get_display_rate(currency=TEST_CURRENCY)
            # ----------------------------------------------------------------------------------------------------------
            rows = []
            # ----------------------------------------------------------------------------------------------------------
            last_Remain = 1  # 这里是循环数据处理的前置数据
            now = datetime.now()
            current_time = now.strftime("%H:%M:%S")
            print("当前时间 =", current_time)
            save_db_flg = True
            for test_year in years:
                single_row_data = {}  # 页面数据行
                anb_year = (TEST_SELECT_ANB) + test_year  # 计算投资起始年龄
                # 判定计算当年是否存在提现，提现参数默认为0，否则需要计算提现后的参数
                try:
                    if param_withdrawal_anb.index(anb_year) >= 0:
                        get_data_index = param_withdrawal_anb.index(anb_year)
                        cal_Extra_Withdrawal = param_Extra_Withdrawal[get_data_index]
                        one_Total_Withdrawal = 0  # 總提取
                        one_Cumulative_Withdrawal = 0  # 累積提取
                        withdrawal_cal = True
                        save_db_flg = False  # 没有提现数据则可作为标准数据写入计算参数组合标准数据库
                    else:
                        one_Total_Withdrawal = 0  # 總提取
                        one_Cumulative_Withdrawal = 0  # 累積提取
                except ValueError as e1:
                    withdrawal_cal = False
                    one_Total_Withdrawal = 0  # 總提取
                    one_Cumulative_Withdrawal = 0  # 累積提取
                    cal_Extra_Withdrawal = 0  # 額外提取
                print("run test Year with : %d" % test_year)
                # ------------------------------------------------------------------------------------------------------
                # Col 1
                one_bonus_base = calStd.get_bonus_base(year=test_year, anb=TEST_SELECT_ANB,
                                                       payment_term=TEST_SELECT_PAYMENT, anb_index=current_anb_index)
                if one_bonus_base is None:
                    one_bonus_base = float(0)
                single_row_data['bonus_base_res'] = one_bonus_base
                # Col 2
                one_bonus_rate = calStd.get_bonus_rate(year=test_year, anb=TEST_SELECT_ANB,
                                                       payment_term=TEST_SELECT_PAYMENT)
                single_row_data['bonus_rate_res'] = round(one_bonus_rate, 5)
                # Col 3
                one_bonus = calStd.get_bonus(l=one_bonus_rate,
                                             k=one_bonus_base,
                                             age_offset=cal_age_offset,
                                             sum_assured=TEST_SUM_ASSURED)
                single_row_data['bonus_res'] = round(one_bonus, 5)
                # Col 4
                one_Death = calStd.get_death(sum_assured=TEST_SUM_ASSURED, bonus=one_bonus)
                single_row_data['death_res'] = round(one_Death, 5)
                # Col 5
                one_GCV_base = calStd.get_gcv_base(year=test_year, anb=TEST_SELECT_ANB,
                                                   payment_term=TEST_SELECT_PAYMENT, anb_index=current_anb_index)
                single_row_data['GCV_base_res'] = one_GCV_base
                # Col 6
                one_GCV_rate = calStd.get_gcv_rate(year=test_year, anb=TEST_SELECT_ANB,
                                                   payment_term=TEST_SELECT_PAYMENT)
                single_row_data['GCV_rate_res'] = round(one_GCV_rate, 5)
                # Col 7
                one_GCV_value = calStd.get_gcv_value(gcv_rate=one_GCV_rate,
                                                     gcv_base=one_GCV_base,
                                                     age_offset=cal_age_offset,
                                                     sum_assured=TEST_SUM_ASSURED,
                                                     args=[TEST_SELECT_ANB, TEST_GENDER, TEST_SMOKER,
                                                           TEST_SELECT_PAYMENT, TEST_SUM_ASSURED])
                single_row_data['GCV_value_res'] = round(one_GCV_value, 5)
                # Col 8
                one_CV = calStd.get_cv(one_bonus, one_GCV_value)
                single_row_data['CV_res'] = round(one_CV, 5)
                # Col 9
                one_SA_0 = calStd.get_sa_0(TEST_SUM_ASSURED)
                single_row_data['SA_0_res'] = round(one_SA_0, 5)
                # Col 10
                sa_year = calStd.get_year(test_year, TEST_SELECT_ANB)
                one_SA_1 = calStd.get_sa_1(TEST_REPEAT_PLAN, sa_year, TEST_SELECT_PAYMENT, TEST_SUM_ASSURED,
                                           TEST_REPEAT_GROWTH_RATE)
                single_row_data['SA_1_res'] = round(one_SA_1, 5)
                # Col 11
                one_SA_2 = calStd.get_sa_2(TEST_REPEAT_PLAN, sa_year, TEST_SELECT_PAYMENT, TEST_SUM_ASSURED,
                                           TEST_REPEAT_GROWTH_RATE)
                single_row_data['SA_2_res'] = round(one_SA_2, 5)
                # Col 12
                one_SA_3 = calStd.get_sa_3(TEST_REPEAT_PLAN, sa_year, TEST_SELECT_PAYMENT, TEST_SUM_ASSURED,
                                           TEST_REPEAT_GROWTH_RATE)
                single_row_data['SA_3_res'] = round(one_SA_3, 5)
                # Col 13
                one_SA_4 = calStd.get_sa_4(TEST_REPEAT_PLAN, sa_year, TEST_SELECT_PAYMENT, TEST_SUM_ASSURED,
                                           TEST_REPEAT_GROWTH_RATE)
                single_row_data['SA_4_res'] = round(one_SA_4, 5)
                # Col 14
                one_SA_5 = calStd.get_sa_5(TEST_REPEAT_PLAN, sa_year, TEST_SELECT_PAYMENT, TEST_SUM_ASSURED,
                                           TEST_REPEAT_GROWTH_RATE)
                single_row_data['SA_5_res'] = round(one_SA_5, 5)
                # Col 15
                one_Total_min_SA = calStd.get_total_min_sa(cal_year=test_year, payment_term=TEST_SELECT_PAYMENT,
                                                           repeat_plan=TEST_REPEAT_PLAN, sa_minimum=cal_sa_minimum)
                single_row_data['Total_min_SA_res'] = round(one_Total_min_SA, 5)
                # Col 16
                # STEP 1 SA0 ~ SA5 数组求和
                sa_array_nums = [one_SA_0, one_SA_1, one_SA_2, one_SA_3, one_SA_4, one_SA_5]
                one_Running_SA = calStd.get_running_sa(nums=sa_array_nums, last_remain=last_Remain)
                single_row_data['Running_SA_res'] = round(one_Running_SA, 5)
                # Col 17
                one_SA_too_low = calStd.get_sa_too_low(one_Running_SA, one_Total_min_SA)
                single_row_data['SA_too_low_res'] = str(one_SA_too_low)
                # Col 18
                one_Year = calStd.get_year(test_year, TEST_SELECT_ANB)
                single_row_data['Year_res'] = one_Year
                # Col 19
                # STEP 1 计算保费折扣
                cal_discount = base_table_top.get_discount(TEST_SELECT_PAYMENT, TEST_SUM_ASSURED)
                # STEP 2 计算保费
                cal_premium = base_table_top.get_premium(anb=TEST_SELECT_ANB, discount=cal_discount,
                                                         sum_assured=TEST_SUM_ASSURED,
                                                         args=[TEST_SELECT_ANB, TEST_GENDER, TEST_SMOKER,
                                                               TEST_SELECT_PAYMENT, TEST_SUM_ASSURED])
                # STEP 3 计算年度保费
                cal_lfree_annual_premium = base_table_top.get_lfree_annual_premium(premium=cal_premium)
                # STEP 4 计算保费AI列数据
                cal_lfree_premium = calStd.get_lfree_premium(anb_year=anb_year, anb=TEST_SELECT_ANB,
                                                             payment_term=TEST_SELECT_PAYMENT,
                                                             repeat_plan=TEST_REPEAT_PLAN,
                                                             lfree_annual_premium=round(cal_lfree_annual_premium, 0),
                                                             repeat_growth_rate=TEST_REPEAT_GROWTH_RATE)
                # STEP 5 需要计算当前保费
                one_Rebate = calStd.get_rebate(cal_year=test_year, promotion=TEST_PROMOTION,
                                               payment_term=TEST_SELECT_PAYMENT, lfree_premium=cal_lfree_premium,
                                               display_rate=cal_display_rate)
                single_row_data['Rebate_res'] = round(one_Rebate, 5)
                # COl 20
                one_EGS_premium = calStd.get_egs_premium(anb_year=anb_year, lfree_premium=cal_lfree_premium,
                                                         display_rate=cal_display_rate,
                                                         rebate=one_Rebate)
                single_row_data['EGS_premium_res'] = round(one_EGS_premium, 5)
                # COl 21
                if len(rows) > 0:
                    last_row = rows[len(rows) - 1]
                    last_Cumulative_premium = last_row['Cumulative_premium_res']
                    one_Cumulative_premium = calStd.get_cumulative_premium(
                        last_Cumulative_premium=last_Cumulative_premium,
                        EGS_premium=one_EGS_premium)
                else:
                    one_Cumulative_premium = calStd.get_cumulative_premium(last_Cumulative_premium=0,
                                                                           EGS_premium=one_EGS_premium)
                single_row_data['Cumulative_premium_res'] = round(one_Cumulative_premium, 5)
                # COl 22
                # 因为 [Adjusted EGS CV = LFREE CV] ==> [LFREE CV = CV] ==> [CV= GCV value + bonus]
                #
                cal_lfree_cv = calStd.get_lfree_cv(cv=one_CV)
                cal_Adjusted_EGS_CV = calStd.get_adjusted_egs_cv(lfree_cv=cal_lfree_cv)
                # Notice： 1 ~ 5A. EGS CV 的计算想需要计算同年期的 Adjusted EGS CV 值
                cal_A_EGS_CV_1 = calStd.get_a_egs_cv_1(repeat_plan=TEST_REPEAT_PLAN,
                                                       ab_year=sa_year, payment_term=TEST_SELECT_PAYMENT,
                                                       repeat_growth_rate=TEST_REPEAT_GROWTH_RATE,
                                                       current_adjusted_egs_cv=cal_Adjusted_EGS_CV)
                cal_A_EGS_CV_2 = calStd.get_a_egs_cv_2(repeat_plan=TEST_REPEAT_PLAN,
                                                       ab_year=sa_year, payment_term=TEST_SELECT_PAYMENT,
                                                       repeat_growth_rate=TEST_REPEAT_GROWTH_RATE,
                                                       current_adjusted_egs_cv=cal_Adjusted_EGS_CV)
                cal_A_EGS_CV_3 = calStd.get_a_egs_cv_3(repeat_plan=TEST_REPEAT_PLAN,
                                                       ab_year=sa_year, payment_term=TEST_SELECT_PAYMENT,
                                                       repeat_growth_rate=TEST_REPEAT_GROWTH_RATE,
                                                       current_adjusted_egs_cv=cal_Adjusted_EGS_CV)
                cal_A_EGS_CV_4 = calStd.get_a_egs_cv_4(repeat_plan=TEST_REPEAT_PLAN,
                                                       ab_year=sa_year, payment_term=TEST_SELECT_PAYMENT,
                                                       repeat_growth_rate=TEST_REPEAT_GROWTH_RATE,
                                                       current_adjusted_egs_cv=cal_Adjusted_EGS_CV)
                cal_A_EGS_CV_5 = calStd.get_a_egs_cv_5(repeat_plan=TEST_REPEAT_PLAN,
                                                       ab_year=sa_year, payment_term=TEST_SELECT_PAYMENT,
                                                       repeat_growth_rate=TEST_REPEAT_GROWTH_RATE,
                                                       current_adjusted_egs_cv=cal_Adjusted_EGS_CV)
                # 计算 A.EGS CV 总计 Total A. EGS CV
                # Total A. EGS CV = Adjusted EGS CV + A. EGS CV 1+A. EGS CV 2+A. EGS CV 3+A. EGS CV 4+A. EGS CV 5
                cal_A_EGS_CV_array = [cal_Adjusted_EGS_CV, cal_A_EGS_CV_1, cal_A_EGS_CV_2, cal_A_EGS_CV_3,
                                      cal_A_EGS_CV_4,
                                      cal_A_EGS_CV_5]
                cal_Total_A_EGS_CV = calStd.get_total_a_egs_cv(nums=cal_A_EGS_CV_array)
                # 计算重新调整CV
                last_re_adjusted_cv = float(0)
                if len(rows) > 0:
                    last_row = rows[len(rows) - 1]
                    last_re_adjusted_cv = last_row['Re_adjusted_CV_res']
                    cal_re_adjusted_cv = calStd.get_re_adjusted_cv(last_re_adjusted_cv=last_re_adjusted_cv,
                                                                   sa_too_low=one_SA_too_low,
                                                                   total_a_egs_cv=cal_Total_A_EGS_CV)
                else:
                    cal_re_adjusted_cv = calStd.get_re_adjusted_cv(last_re_adjusted_cv=0, sa_too_low=one_SA_too_low,
                                                                   total_a_egs_cv=cal_Total_A_EGS_CV)
                one_annual_withdrawal = calStd.get_annual_withdrawal(total_withdrawal=one_Total_Withdrawal,
                                                                     display_rate=cal_display_rate,
                                                                     re_adjusted_CV=cal_re_adjusted_cv)
                single_row_data['Annual_withdrawal_res'] = one_annual_withdrawal
                # col 23
                if len(rows) > 0:
                    last_row = rows[len(rows) - 1]
                    last_Remain = last_row["Remain_res"]
                    one_Remain = calStd.get_remain(cal_year=test_year, last_remain=last_Remain,
                                                   annual_withdrawal=one_annual_withdrawal)
                else:
                    last_Remain = 1
                    one_Remain = calStd.get_remain(cal_year=test_year, last_remain=last_Remain,
                                                   annual_withdrawal=one_annual_withdrawal)
                single_row_data['Remain_res'] = round(one_Remain, 2)
                # Col 24
                one_Balance = calStd.get_Balance(remain=one_Remain, re_adjusted_CV=cal_re_adjusted_cv)
                single_row_data['Balance_res'] = round(one_Balance, 5)
                # Col 25
                one_LFREE_premium = cal_lfree_premium
                single_row_data['LFREE_premium_res'] = round(one_LFREE_premium, 5)
                # Col 26
                one_LFREE_CV = cal_lfree_cv
                single_row_data['LFREE_CV_res'] = round(one_LFREE_CV, 5)
                # Col 27
                one_Adjusted_EGS_CV = cal_Adjusted_EGS_CV
                single_row_data['Adjusted_EGS_CV_res'] = round(one_Adjusted_EGS_CV, 2)
                # Col 28
                one_A_EGS_CV_1 = cal_A_EGS_CV_1
                single_row_data['A_EGS_CV_1_res'] = round(one_A_EGS_CV_1, 5)
                # Col 29
                one_A_EGS_CV_2 = cal_A_EGS_CV_2
                single_row_data['A_EGS_CV_2_res'] = round(one_A_EGS_CV_2, 5)
                # Col 30
                one_A_EGS_CV_3 = cal_A_EGS_CV_3
                single_row_data['A_EGS_CV_3_res'] = round(one_A_EGS_CV_3, 5)
                # Col 31
                one_A_EGS_CV_4 = cal_A_EGS_CV_4
                single_row_data['A_EGS_CV_4_res'] = round(one_A_EGS_CV_4, 5)
                # Col 32
                one_A_EGS_CV_5 = cal_A_EGS_CV_5
                single_row_data['A_EGS_CV_5_res'] = round(one_A_EGS_CV_5, 5)
                # Col 33
                one_Total_A_EGS_CV = cal_Total_A_EGS_CV
                single_row_data['Total_A_EGS_CV_res'] = round(one_Total_A_EGS_CV, 5)
                # Col 34
                one_re_adjusted_cv = cal_re_adjusted_cv
                single_row_data['Re_adjusted_CV_res'] = round(one_re_adjusted_cv, 5)
                # Col 35
                one_Balance_before_withdraw = calStd.get_balance_before_withdraw(re_adjusted_CV=last_re_adjusted_cv,
                                                                                 remain=one_Remain)
                single_row_data['Balance_before_withdraw_res'] = round(one_Balance_before_withdraw, 5)
                # Col 36
                one_Retired = calStd.get_retired(anb_year=anb_year,
                                                 withdrawal_starting_anb=TEST_WITHDRAWAL_STARTING_ANB)
                single_row_data['Retired_res'] = str(one_Retired)
                # Col 37
                one_Income = calStd.get_income(retired=one_Retired, anb_year=anb_year, surrender_anb=TEST_SURRENDER_ANB,
                                               withdrawal_starting_anb=TEST_WITHDRAWAL_STARTING_ANB,
                                               withdrawal_years=TEST_WITHDRAWAL_YEARS)
                single_row_data['Income_res'] = str(one_Income)
                # Col 38
                one_Inflation_factor = calStd.get_inflation_factor(retired=one_Retired, inflation=TEST_INFLATION,
                                                                   anb_year=anb_year,
                                                                   withdrawal_starting_anb=TEST_WITHDRAWAL_STARTING_ANB)
                single_row_data['Inflation_factor_res'] = one_Inflation_factor
                # Col 39
                one_Inflated_income = calStd.get_inflated_income(income=one_Income,
                                                                 fst_year_withdrawal=TEST_FST_YEAR_WITHDRAWAL,
                                                                 display_rate=cal_display_rate,
                                                                 inflation_factor=one_Inflation_factor)
                single_row_data['Inflated_income_res'] = one_Inflated_income
                # Col 40
                one_Withdrawal = calStd.get_withdrawal(inflated_income=one_Inflated_income,
                                                       extra_withdrawal=cal_Extra_Withdrawal,
                                                       display_rate=cal_display_rate,
                                                       balance_before_withdraw=one_Balance_before_withdraw)
                single_row_data['one_Withdrawal_res'] = one_Withdrawal
                # ------------------------------------------------------------------------------------------------------
                # chg_tab Area
                # col A
                single_row_data['chgtab_Year_res'] = test_year
                # col B
                single_row_data['chgtab_ANB_res'] = anb_year
                # col C
                one_Annual_Contribution = one_EGS_premium
                single_row_data['chgtab_Annual_Contribution_res'] = one_Annual_Contribution
                # col D
                single_row_data['chgtab_Extra_Withdrawal_res'] = cal_Extra_Withdrawal
                # col E
                single_row_data['chgtab_Total_Withdrawal_res'] = one_Total_Withdrawal
                # col F
                single_row_data['chgtab_Cumulative_Withdrawal_res'] = one_Cumulative_Withdrawal
                # col G
                # IF(AND(B61<102,B61<=F3),AH61,0)*C45
                one_chgtab_Balance = one_Balance if (anb_year < 102 and anb_year <= TEST_SURRENDER_ANB) else 0
                one_chgtab_Balance = one_chgtab_Balance * cal_display_rate
                single_row_data['chgtab_Balance_res'] = round(one_chgtab_Balance, 3)
                # col H
                # IF(AND(B61<102,B61<=$F$3,NOT(AA61)),N61*AG61,0)*$C$45
                one_Death_Benefit = one_Death * one_Remain if ((anb_year < 102) and
                                                               (anb_year <= TEST_SURRENDER_ANB) and
                                                               (not one_SA_too_low)) else 0
                one_Death_Benefit = one_Death_Benefit * cal_display_rate
                single_row_data['chgtab_Death_Benefit_res'] = round(one_Death_Benefit, 3)
                # chg_tab Area
                # ------------------------------------------------------------------------------------------------------
                # 验证是否需要提现计算
                if withdrawal_cal:
                    # 1 Balance before withdraw AS61
                    one_Balance_before_withdraw = calStd.get_balance_before_withdraw(re_adjusted_CV=cal_re_adjusted_cv,
                                                                                     remain=one_Remain)
                    # 2 Withdrawal AX
                    one_Withdrawal = calStd.get_withdrawal(inflated_income=one_Inflated_income,
                                                           extra_withdrawal=cal_Extra_Withdrawal,
                                                           display_rate=cal_display_rate,
                                                           balance_before_withdraw=one_Balance_before_withdraw)
                    # 3 Total Withdrawal E61 IF(B64<102,AX64,0)*C45
                    one_Total_Withdrawal = one_Withdrawal * cal_display_rate if anb_year < 102 else 0
                    # 4 Cumulative Withdrawal F61 IF(AND(B64<102,B64<=F3),F63+E64,0)
                    if len(rows) > 0:
                        last_row = rows[len(rows) - 1]
                        last_Cumulative_Withdrawal = last_row['chgtab_Cumulative_Withdrawal_res']
                        one_Cumulative_Withdrawal = last_Cumulative_Withdrawal + one_Total_Withdrawal if anb_year < 102 else 0
                    else:
                        one_Cumulative_Withdrawal = 0
                    single_row_data['chgtab_Cumulative_Withdrawal_res'] = one_Cumulative_Withdrawal
                    # 5 Annual withdrawal AF61
                    one_annual_withdrawal = calStd.get_annual_withdrawal(total_withdrawal=one_Total_Withdrawal,
                                                                         display_rate=cal_display_rate,
                                                                         re_adjusted_CV=cal_re_adjusted_cv)
                    single_row_data['Annual_withdrawal_res'] = one_annual_withdrawal
                    # 6 Remain AG61
                    if len(rows) > 0:
                        last_row = rows[len(rows) - 1]
                        last_Remain = last_row["Remain_res"]
                        one_Remain = calStd.get_remain(cal_year=test_year, last_remain=last_Remain,
                                                       annual_withdrawal=one_annual_withdrawal)
                    else:
                        last_Remain = 1
                        one_Remain = calStd.get_remain(cal_year=test_year, last_remain=last_Remain,
                                                       annual_withdrawal=one_annual_withdrawal)
                    single_row_data['Remain_res'] = round(one_Remain, 3)
                    # 7 Balance G61
                    one_Balance = calStd.get_Balance(remain=one_Remain, re_adjusted_CV=cal_re_adjusted_cv)
                    single_row_data['Balance_res'] = round(one_Balance, 3)
                    single_row_data['Balance_before_withdraw_res'] = one_Balance_before_withdraw
                    single_row_data['one_Withdrawal_res'] = one_Withdrawal
                else:
                    one_Total_Withdrawal = 0  # 總提取
                    one_Cumulative_Withdrawal = 0  # 累積提取
                    cal_Extra_Withdrawal = 0  # 額外提取
                # ------------------------------------------------------------------------------------------------------
                # Col 41 这里需要最后去计算，数据来源前置总表格
                cal_Cashflow = calStd.get_cashflow(surrender_anb=TEST_SURRENDER_ANB, anb=TEST_SELECT_ANB,
                                                   anb_year=anb_year, total_withdrawal=one_Total_Withdrawal,
                                                   annual_contribution=one_Annual_Contribution,
                                                   balance=one_Balance)
                single_row_data['Cashflow_res'] = cal_Cashflow
                # ------------------------------------------------------------------------------------------------------
                rows.append(single_row_data)

                # 增加年期判定
                if anb_year == 110:
                    break
            # -----------------------------------------------------------------------------------------------------------
            now_2 = datetime.now()
            current_time_2 = now_2.strftime("%H:%M:%S")
            print("当前时间 =", current_time_2)
            str_json_data = json.dumps(rows, ensure_ascii=False)
            if save_db_flg:
                dict_db.insert_cal_json_data([str_json_key, str_json_data, str(datetime.now().strftime("%f"))])
            # 保持一致同步，写入到RDS中
            RedisConfig.push_val_intimes(rds_cfg, key=f'freeL_hash_key:{str_json_key}', val=str_json_data,
                                         json_flg=True, inTimes=60 * 60)
            return {"code": 200, "data": str_json_data}
        except Exception as e:
            print(e)
